﻿Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports PkgExec.SSISPackage
Imports PkgExec

Public Class DTSVariable
    Public ObjectName As String
    Public DataType As System.TypeCode
    Public VarNameSpace As String

    Public BufferId As Integer


End Class
Public Class SSISPackage
    Public File As String

    Public varList As New List(Of DTSVariable)

    Public PackageRowIdBufferId As Integer
    Dim App As New Microsoft.SqlServer.Dts.Runtime.Application

    Dim P As Microsoft.SqlServer.Dts.Runtime.Package '= App.LoadPackage(File, Nothing)

    Sub ClearVarList()
        varList.Clear()
    End Sub
    Public Sub GetVarList()
        ClearVarList()
        If (File = "") Then
            Return
        End If

        P = App.LoadPackage(File, Nothing)

        Dim Var As Microsoft.SqlServer.Dts.Runtime.Variables = P.Variables()
        For Each V As Microsoft.SqlServer.Dts.Runtime.Variable In Var
            If (V.Namespace = "User") Then
                Dim NewVar As DTSVariable = New DTSVariable
                NewVar.ObjectName = V.Name
                NewVar.VarNameSpace = V.Namespace
                NewVar.DataType = V.DataType
                varList.Add(NewVar)
            End If
        Next

    End Sub
    Public Sub SetColumnMapping(ByVal ColumnMapping As String, ByRef input As IDTSInput100, ByRef BufferManager As IDTSBufferManager100)
        Dim MappingAtt() As String

        MappingAtt = Split(ColumnMapping, "|")
        For Each MappingOn As String In MappingAtt
            If (MappingOn = "") Then
                Exit For
            End If
            Dim ThisAtt = Split(MappingOn, ":")

            If (ThisAtt(1) <> "<Not Mapped>") Then
                If (ThisAtt(1) = "<PackageReturnId>") Then
                    For Each ColOn As IDTSInputColumn100 In input.InputColumnCollection
                        If (ColOn.Name = ThisAtt(0)) Then
                            PackageRowIdBufferId = BufferManager.FindColumnByLineageID(input.Buffer, ColOn.LineageID)
                            Exit For

                        End If
                    Next
                Else
                    For Each VarOn As DTSVariable In varList
                        If (VarOn.ObjectName = ThisAtt(1)) Then
                            For Each ColOn As IDTSInputColumn100 In input.InputColumnCollection
                                If (ColOn.Name = ThisAtt(0)) Then
                                    VarOn.BufferId = BufferManager.FindColumnByLineageID(input.Buffer, ColOn.LineageID)
                                    Exit For
                                End If
                            Next
                        End If
                    Next
                End If
            End If
        Next
    End Sub

    Sub ExecPackage(ByVal RowOn As RowData)



        Dim VarDisp As Microsoft.SqlServer.Dts.Runtime.VariableDispenser = P.VariableDispenser()
        For Each VarOn As DTSVariable In VarList
            VarDisp.LockForRead(VarOn.ObjectName)
        Next


        Dim vars As Microsoft.SqlServer.Dts.Runtime.Variables = Nothing
        VarDisp.GetVariables(vars)

        Dim myVar As Microsoft.SqlServer.Dts.Runtime.Variable
        For Each ColOn As ColumnData In RowOn.DataList
            For Each myVar In vars
                If (myVar.Name = ColOn.DtsVariable.ObjectName) Then
                    myVar.Value = ColOn.ColumnValue
                    Exit For
                End If

            Next

        Next


        vars.Unlock()

        Dim Res As Microsoft.SqlServer.Dts.Runtime.DTSExecResult
        Res = P.Execute()
        RowOn.Result = Res
    End Sub
End Class
